from sqlalchemy import Column, String, create_engine, Date, Time, func
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
import requests,json,configparser,os,time
import pypyodbc

# 创建对象的基类:
Base = declarative_base()

# 定义Line对象:
class Line(Base):
    # 表的名字:
    __tablename__ = 'Alarm202004'.format(datetime.today().month)
    # 表的结构:
    AlarmDate = Column(Date,nullable=False)
    AlarmTime = Column(Time,nullable=False,primary_key=True)
    AlarmType = Column(String(12),nullable=False)
    AcrDate = Column(Date)
    VarName = Column(String(30),nullable=False)

#定义数据库连接
def mdb_conn(db_name, password = ""):
    """
    功能：创建数据库连接
    :param db_name: 数据库名称
    :param db_name: 数据库密码，默认为空
    :return: 返回数据库连接
    """
    str = 'Driver={Microsoft Access Driver (*.mdb)};PWD' + password + ";DBQ=" + db_name
    conn = pypyodbc.win_connect_mdb(str)
    return conn

def mdb_sel(cur, sql):
    """
    功能：向数据库查询数据
    :param cur: 游标
    :param sql: sql语句
    :return: 查询结果集
    """
    try:
        cur.execute(sql)
        return cur.fetchall()
    except:
        return []

def upload(d):
    # 数据库API的url
    flag = False
    times = 0
    while not flag:
        url = 'http://127.0.0.1:8080/upload/'
        Dtime = (d.AlarmDate + " " + d.AlarmTime).strip()
        my_data = {'line':line,'AlarmDate':d.AlarmDate.strip(), 'AlarmTime':d.AlarmTime.strip(), 'AlarmType':d.AlarmType, 'VarName':d.VarName, 'DateTime':Dtime}
        r = requests.post(url,data=json.dumps(my_data))
        if r.status_code == 200:
            flag = True
        else:
            times =+ 1
            print('上传失败，重新第{0}次上传！！！'.format(times))
            time.sleep(10)



if __name__ == '__main__':
    # 取得参数
    conf = configparser.ConfigParser()
    if os.path.exists('config.ini'):
        conf.read('config.ini')
        line = conf['DEFAULT']['line']
        onetime = int(conf['DEFAULT']['onetime'])
        interval = int(conf['DEFAULT']['interval'])
        startby = int(conf['DEFAULT']['startby'])
    else:
        line = input('请输入线体：')
        interval = input('请输入时间间隔（分钟）:')
        onetime = input('请输入默认一次传送数据的数量（条）：')
        startby = 0
        conf["DEFAULT"] = {'line': line,
                            'interval': interval,
                           'onetime':onetime,
                           'startby':0
                             }
    # 初始化数据库连接:(需要手动设置DSN)
    engine = create_engine(r'access+pyodbc://w2')
    # 创建DBSession类型:
    DBSession = sessionmaker(bind=engine)
    # 创建session对象:
    session = DBSession()
    count = session.query(func.count(Line.AlarmDate)).filter(Line.AcrDate == None).first()
    #count = session.query(func.count(Line.AlarmDate)).first()
    print('数据表里合计有{0}条有效数据'.format(count[0]))
    num = 0
    while True:
        print('起点是：',startby)
        if startby < count[0]:
            # 查询onetime数量的数据
            #data = session.query(Line).filter(Line.AcrDate == None).order_by(Line.AlarmDate,Line.AlarmTime).all()
            data = session.query(Line).filter(Line.AcrDate == None).slice(startby,startby + onetime).all()
            print('已取得{0}条数据'.format(len(data)))
            print('现在的起点是：',startby)
            for d in data:
                upload(d)
                num = num + 1
                if num % 10 == 0:
                    print('已上传{0}条数据~~~~'.format(num))
            print('亲们，合计已上传{0}条数据~~~~'.format(startby + onetime))
            startby = startby + onetime
            conf.set('DEFAULT','startby',str(startby))
            conf.write(open("config.ini", "w"))
            # startby = startby + onetime
            # conf["DEFAULT"]['startby'] = str(startby)
        else:
            startby = count[0]
            print('所有数据已上传完毕~~~~','等待60秒~~~~')
            time.sleep(60)
            count = session.query(func.count(Line.VarName)).filter(Line.AcrDate == None).first()
            conf.set('DEFAULT', 'startby', str(startby))
            conf.write(open("config.ini", "w"))





